import org.authsum.stitches.Helper
import grails.util.GrailsUtil
import org.authsum.stitches.domain.*
import org.authsum.stitches.*
import org.authsum.stitches.external.*
import org.codehaus.groovy.grails.commons.ApplicationHolder
import java.io.*
import org.springframework.core.io.DefaultResourceLoader
import org.springframework.core.io.ResourceLoader
import org.springframework.core.io.Resource
import org.apache.commons.io.IOUtils

import java.sql.Connection;
import java.sql.DriverManager;
import org.apache.commons.io.FileUtils


import java.io.File;
import java.sql.Connection;
import java.sql.ResultSet
import java.sql.SQLException
import java.sql.Statement;
import java.util.Iterator
import java.util.List

class BootStrap {
	
	def stitchesService
	
	def grailsApplication
	
	def init = { servletContext ->
		
		if (GrailsUtil.environment == 'test') {
			return
			
		}
		def helper = grailsApplication.mainContext.getBean('helper')
		
		
		def resourcePath = "/resources/"
		def resource = ApplicationHolder.application.parentContext.getResource("classpath:$resourcePath")
		def csvFilePath = resource.getFile().absolutePath
		
		
		
		//if the contentdef already exists, just return
		ContentDefRequest contentDefRequest = new ContentDefRequest()
		contentDefRequest.contentDefName = 'zipcode'
		ContentDefResponse contentDefResponse = stitchesService.findContentDefById(contentDefRequest)
		Long contentDefId
		Long zipAttributeId 
		Long longLatAttributeId
		Long cityNameAttributeId
		if (contentDefResponse.responseCode != '404')  {
			return
			def contentDefHolder = contentDefResponse.getContentDefHolders().getContentDefHolder().get(0)
			contentDefId = contentDefHolder.id
			contentDefHolder.attributeHolders.getAttributeHolder().each {
				println "x=" + it
				if (it.name == 'zipcode') {
					zipAttributeId = it.id
				} else if (it.name == 'long-lat') {
					longLatAttributeId = it.id
				}
				
			}
			
		} else {
			
			//create a content def of a zipcode
			contentDefId = helper.getContentDefId(name:"zipcode")
			//create a zip code attribute
			zipAttributeId = helper.getAttributeId(contentDefId:contentDefId,attributeName:"zipcode",valueType:"String")
			//create a long lat attribute
			longLatAttributeId = helper.getAttributeId(contentDefId:contentDefId,attributeName:"long-lat",valueType:"Longitude & Latitude",required:true)	
			cityNameAttributeId = helper.getAttributeId(contentDefId:contentDefId,attributeName:"cityName",valueType:"String")
			
		}
		
		assert longLatAttributeId > 0
		
		runImport(csvFilePath:csvFilePath,contentDefId:contentDefId,zipAttributeId:zipAttributeId,longLatAttributeId:longLatAttributeId,cityNameAttributeId:cityNameAttributeId )
		
		
		
		
	}
	def destroy = {
	}
	protected static String getString(String colName, ResultSet rs) {
		String value = null;
		try {
			value = rs.getString(colName)
		} catch (SQLException e) {
		}
		return value;
	}
	
	
	protected static Connection getConnection(String filePath) throws Exception {
		
		Class.forName("org.relique.jdbc.csv.CsvDriver");
		Connection connection = DriverManager.getConnection("jdbc:relique:csv:"
				+ filePath);
		return connection;
	}
	
	private void runImport(Map args) throws Exception {
		
		def helper = grailsApplication.mainContext.getBean('helper')
		//String fileName = file.getName();
		Connection connection = null;
		try {
			connection = getConnection(args.csvFilePath);
			Statement statement = connection.createStatement();
			//each env has a different zipcode file so I have huge 43k in prod, and small one in dev
			String zipcodefile = grailsApplication.config.zipcodefile
			String sql = "SELECT * FROM " + zipcodefile
			ResultSet rs = statement.executeQuery(sql);

			while (rs.next()) {
			
				
				
				String zip = getString("zip",rs)
				
				
				String state = getString("state",rs)
					String latitude = getString("latitude",rs)
					String longitude = getString("longitude",rs)
					String city = getString("city",rs);
					if ((zip != null)&&(latitude != null)&&(longitude!= null)) {
						Long contentId = helper.getContentId(contentDefId:args.contentDefId,name:zip)
						Long contentVersionId = helper.getContentVersionId(contentId:contentId)
						def avrequest = new AttributeValuesRequest()
						helper.addAttributeValueHolder(avrequest,[contentVersionId:contentVersionId,attributeId:args.longLatAttributeId,latitude:latitude.toDouble(),longitude:longitude.toDouble()])				
						helper.addAttributeValueHolder(avrequest,[contentVersionId:contentVersionId,attributeId:args.zipAttributeId,valueAsString:zip])
						helper.addAttributeValueHolder(avrequest,[contentVersionId:contentVersionId,attributeId:args.cityNameAttributeId,valueAsString:city])

						def response = stitchesService.saveAttributeValues(avrequest)
						assert response.responseCode == '200'
					}
			
				
			}
			
			//statement.close();
		} catch (Exception e) {
			e.printStackTrace();
			
			throw e;
		} finally {
			try {
				connection.close();
			} catch (SQLException e) {
			}
		}
	}
	
	
	
} 